﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using System.Net;
using MesGLApp.DAL.RoleDB;
using MesGLApp.DAL.MesDB;
using MesGLApp.DAL.MesDTO;
using MesGLApp.Tools;
using MesGLApp.View;
using SqlSugar;

namespace MesGLApp.BLL
{
    public class BaseControler
    {
        private SqlSugarClient Db = SqlSugares.GetInstance();
        /// <summary>
        /// 查询服务器当前时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetCurrDateTime()
        {
            using (RoleDB ctx = new RoleDB())
            {
                string tmpSql = "select getdate() as curr_date";
                var res = ctx.Database.SqlQuery<DateTime>(tmpSql);
                return res.FirstOrDefault();
            }
        }

        /// <summary>
        /// 查询所有状态
        /// </summary>
        /// <returns></returns>
        public List<StatusType> GetAllStatus()
        {
            List<StatusType> typeList = new List<StatusType>();
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.StatusTypes;
                if (query != null && query.Count() > 0)
                {
                    foreach (var item in query)
                    {
                        item.StatusList = new List<StatusInfo>();
                        var typeQuery = ctx.StatusInfos.Where(info => info.StypeID == item.ID);
                        if (typeQuery != null && typeQuery.Count() > 0)
                        {
                            item.StatusList.AddRange(typeQuery);
                        }
                        typeList.Add(item);
                    }
                }
            }
            return typeList;
        }

        /// <summary>
        /// 查询所有状态
        /// </summary>
        /// <returns></returns>
        public List<StatusInfo> SearchAllStatus()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.StatusInfos;
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<StatusInfo>();
                }
            }
        }


        /// <summary>
        /// 根据状态组别查询该组别以下的所有状态
        /// </summary>
        /// <param name="typeName">状态组别的名称</param>
        /// <returns></returns>
        public List<StatusInfo> GetStatusInfoByTypeName(string typeName)
        {
            using (MesDB ctx = new MesDB())
            {
                var query = from a in ctx.StatusTypes
                            join b in ctx.StatusInfos on a.ID equals b.StypeID into res
                            from ab in res
                            where a.StypeName == typeName
                            select ab;
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<StatusInfo>();
                }
            }
        }

        /// <summary>
        /// 查询所有站点信息
        /// </summary>
        /// <returns>该方法只返回最终站点（即子站点）</returns>
        public List<SiteInfo> SearchAllSiteInfo()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.SiteInfos.Where(s => s.IsFinal == 1);
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<SiteInfo>();
                }
            }
        }

        /// <summary>
        /// 查询所有站点信息
        /// </summary>
        /// <returns>该方法只返回父站点</returns>
        public List<SiteInfo> SearchAllParentSiteInfo()
        {
            using (MesDB ctx = new MesDB())
            {
                var query = ctx.SiteInfos.Where(s => s.ParentID == 0);
                if (query != null && query.Count() > 0)
                {
                    return query.ToList();
                }
                else
                {
                    return new List<SiteInfo>();
                }
            }
        }

        /// <summary>
        /// 查询所有站点组
        /// </summary>
        /// <returns></returns>
        public List<SiteGroupDTO> GetAllSiteGroup()
        {
            List<SiteGroupDTO> resList = new List<SiteGroupDTO>();
            using (MesDB ctx = new MesDB())
            {
                var query = from a in ctx.SiteGroups
                            join b in ctx.SiteInfoGroups on a.ID equals b.GroupID into res1
                            from ab in res1
                            join c in ctx.SiteInfos on ab.SiteID equals c.ID into res2
                            from bc in res2
                            select new { A = a, AB = ab, BC = bc };
                if (query != null && query.Count() > 0)
                {
                    var uniqueA = query.Select(x => x.A).Distinct();
                    if (uniqueA != null && uniqueA.Count() > 0)
                    {
                        foreach (var item in uniqueA)
                        {
                            SiteGroupDTO tmpBean = new SiteGroupDTO();
                            EntityTools.CopyEntity<SiteGroup,SiteGroupDTO>(item,ref tmpBean);
                            var tmpQuery = query.Where(x => x.A.ID == tmpBean.ID);
                            if (tmpQuery != null)
                            {
                                tmpBean.SiteInfoList = new List<SiteInfoGroupDTO>();
                                foreach (var tmpItem in tmpQuery)
                                {
                                    SiteInfoGroupDTO siteInfoGroupDTO = new SiteInfoGroupDTO();
                                    EntityTools.CopyEntity<SiteInfoGroup, SiteInfoGroupDTO>(tmpItem.AB,ref siteInfoGroupDTO);
                                    siteInfoGroupDTO.SiteInfo = tmpItem.BC;
                                    tmpBean.SiteInfoList.Add(siteInfoGroupDTO);
                                }
                            }

                            resList.Add(tmpBean);
                        }
                    }
                }
            }
            return resList;
        }


        /// <summary>
        /// 新增更新日志
        /// </summary>
        /// <param name="tableName">资料表名</param>
        /// <param name="id">记录的ID</param>
        /// <param name="beforeData">更新前的值</param>
        /// <param name="afterData">更新后的值</param>
        /// <param name="changeType">类型</param>
        /// <param name="changeUser">操作者</param>
        /// <param name="changeDate">更新日期</param>
        /// <returns></returns>
        public MesLog InitMesLog(string tableName,int id,string beforeData,string afterData,string changeType,string changeUser,DateTime? changeDate = null)
        { 
            MesLog resLog = new MesLog();
            resLog.ID = 0;
            resLog.TableName = tableName;
            resLog.TableID = id;
            resLog.BeforeData = beforeData;
            resLog.AfterData = afterData;
            resLog.ChangeType = changeType;
            resLog.ChangeUser = changeUser;
            if (changeDate.HasValue)
            {
                resLog.ChangeDate = changeDate.Value;
            }
            else
            {
                resLog.ChangeDate = GetCurrDateTime();
            }
            return resLog;
        }


        /// <summary>
        /// 根据登陆用户查询该用户可以操作的站点
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<SiteInfo> SearchSiteInfoForUserID(int userID)
        {
            List<SiteInfo> resList = new List<SiteInfo>();
            using (MesDB ctx = new MesDB())
            {
                var query = from a in ctx.SiteInfos
                            join b in ctx.UserSite on a.ID equals b.SiteID into res1
                            from ab in res1
                            where ab.UserID == userID
                            select a;
                if (query != null && query.Count() > 0)
                {
                    resList.AddRange(query);
                }
            }
            return resList;
        }

        /// <summary>
        /// 查询角色的权限部门
        /// </summary>
        /// <returns></returns>
        public List<int> SelectUserDept(int id = 0)
        {
            List<int> deptIds = new List<int>();
            string sql = $@"SELECT
                *
            FROM
                base_dept
            WHERE
                id IN (
                    SELECT
                        value AS depts_id
                    FROM
                        STRING_SPLIT(
                            (
                                SELECT
                                    LEFT(depts_id_list, LEN(depts_id_list) - 1) AS depts_id_list
                                FROM
                                    (SELECT
                                        STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
                                    FROM
                                        base_user_role T1
                                        LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
                                    WHERE
                                        T1.base_user_id = {LoginFrm.LoginUser.id}) AS subquery
                            ), ','
                        )
                )";
            if (id != 0)
            {
                sql = $@"SELECT
                    *
                FROM
                    base_dept
                WHERE
                    id IN (
                        SELECT
                            value AS depts_id
                        FROM
                            STRING_SPLIT(
                                (
                                    SELECT
                                        LEFT(depts_id_list, LEN(depts_id_list) - 1) AS depts_id_list
                                    FROM
                                        (SELECT
                                            STRING_AGG(T2.depts_id, ',') WITHIN GROUP (ORDER BY T2.depts_id) AS depts_id_list
                                        FROM
                                            base_user_role T1
                                            LEFT JOIN base_role T2 ON T1.base_role_id = T2.id
                                        WHERE
                                            T1.base_user_id = {id}) AS subquery
                                ), ','
                            )
                    )";
            }
            var list = Db.Context.SqlQueryable<object>(sql).ToList();
            dynamic dylist = list;
            for (int i = 0; i < list.Count; i++)
            {
                deptIds.Add(dylist[i].id);
            }
            return deptIds;
        }


    }
}
